/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.informet.ipd.modelo.dao;

import com.informet.ipd.modelo.vo.MATareaMVO;
import com.informet.ipd.modelo.vo.MATareaVO;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JOptionPane;

/**
 *
 * @author LAPTOP01
 */
public class MATareaDao  extends DBase{




    Connection conn = null;

    public MATareaDao() {

    super();
    }


    public  double mayor(MATareaVO vo){
        double mayor=0;
    	if(vo.getNUM_MES1MEATA()>vo.getNUM_MES2MEATA()){
    	mayor=vo.getNUM_MES1MEATA();
    	}else{
    		mayor=vo.getNUM_MES2MEATA();
    	}
    	
    	if(mayor<vo.getNUM_MES3MEATA()){
    		mayor=vo.getNUM_MES3MEATA();
    	}
    	if(mayor<vo.getNUM_MES4MEATA()){
    		mayor=vo.getNUM_MES4MEATA();
    	}
    	if(mayor<vo.getNUM_MES5MEATA()){
    		mayor=vo.getNUM_MES5MEATA();
    	}
    	if(mayor<vo.getNUM_MES6MEATA()){
    		mayor=vo.getNUM_MES6MEATA();
    	}
    	
    	if(mayor<vo.getNUM_MES7MEATA()){
    		mayor=vo.getNUM_MES7MEATA();
    	}
    	if(mayor<vo.getNUM_MES8MEATA()){
    		mayor=vo.getNUM_MES8MEATA();
    	}
    	
    	if(mayor<vo.getNUM_MES9MEATA()){
    		mayor=vo.getNUM_MES9MEATA();
    	
    	}
    	if(mayor<vo.getNUM_MES10MEATA()){
    		mayor=vo.getNUM_MES10MEATA();
    	}
    	if(mayor<vo.getNUM_MES11MEATA()){
    		mayor=vo.getNUM_MES11MEATA();
    	}
    	if(mayor<vo.getNUM_MES12MEATA()){
    		mayor=vo.getNUM_MES12MEATA();
    	}
    	return mayor;
    	
    }




    public  double mayorM(MATareaMVO vo){
        double mayor=0;
    	if(vo.getNUM_MES1MEATAM()>vo.getNUM_MES2MEATAM()){
    	mayor=vo.getNUM_MES1MEATAM();
    	}else{
    		mayor=vo.getNUM_MES2MEATAM();
    	}

    	if(mayor<vo.getNUM_MES3MEATAM()){
    		mayor=vo.getNUM_MES3MEATAM();
    	}
    	if(mayor<vo.getNUM_MES4MEATAM()){
    		mayor=vo.getNUM_MES4MEATAM();
    	}
    	if(mayor<vo.getNUM_MES5MEATAM()){
    		mayor=vo.getNUM_MES5MEATAM();
    	}
    	if(mayor<vo.getNUM_MES6MEATAM()){
    		mayor=vo.getNUM_MES6MEATAM();
    	}

    	if(mayor<vo.getNUM_MES7MEATAM()){
    		mayor=vo.getNUM_MES7MEATAM();
    	}
    	if(mayor<vo.getNUM_MES8MEATAM()){
    		mayor=vo.getNUM_MES8MEATAM();
    	}

    	if(mayor<vo.getNUM_MES9MEATAM()){
    		mayor=vo.getNUM_MES9MEATAM();

    	}
    	if(mayor<vo.getNUM_MES10MEATAM()){
    		mayor=vo.getNUM_MES10MEATAM();
    	}
    	if(mayor<vo.getNUM_MES11MEATAM()){
    		mayor=vo.getNUM_MES11MEATAM();
    	}
    	if(mayor<vo.getNUM_MES12MEATAM()){
    		mayor=vo.getNUM_MES12MEATAM();
    	}
    	return mayor;

    }
    
    private void setMTarea(MATareaVO bean, ResultSet rs) throws SQLException {
   bean.setNUM_MEATA_PK(rs.getInt("NUM_MEATA_PK"));
   bean.setTX_DESCATAREA(rs.getString("TX_DESCATAREA"));
   bean.setTX_UMAATAREA(rs.getString("TX_UMAATAREA"));
   bean.setNUM_MES1MEATA(rs.getDouble("NUM_MES1MEATA"));
   bean.setNUM_MES2MEATA(rs.getDouble("NUM_MES2MEATA"));
   bean.setNUM_MES3MEATA(rs.getDouble("NUM_MES3MEATA"));
   bean.setNUM_MES4MEATA(rs.getDouble("NUM_MES4MEATA"));
   bean.setNUM_MES5MEATA(rs.getDouble("NUM_MES5MEATA"));
   bean.setNUM_MES6MEATA(rs.getDouble("NUM_MES6MEATA"));
   bean.setNUM_MES7MEATA(rs.getDouble("NUM_MES7MEATA"));
   bean.setNUM_MES8MEATA(rs.getDouble("NUM_MES8MEATA"));
   bean.setNUM_MES9MEATA(rs.getDouble("NUM_MES9MEATA"));
   bean.setNUM_MES10MEATA(rs.getDouble("NUM_MES10MEATA"));
   bean.setNUM_MES11MEATA(rs.getDouble("NUM_MES11MEATA"));
   bean.setNUM_MES12MEATA(rs.getDouble("NUM_MES12MEATA"));
   bean.setTX_TIPOATAREA1(rs.getInt("TX_TIPOATAREA1"));

    }

    private void setMuestraM(MATareaVO bean, ResultSet rs) throws SQLException {
        bean.setNUM_MEATA_PK(rs.getInt("NUM_MEATA_PK"));
    }


    private void setMuestraMod(MATareaVO bean, ResultSet rs) throws SQLException {
        bean.setNUM_MEATA_PK(rs.getInt("NUM_MEATAM_PK"));
    }


    private void setMetaActividadmax(MATareaVO bean, ResultSet rs) throws SQLException {
   bean.setNUM_MEATA_PK(rs.getInt("NUM_MEATA_PK"));

    }

    



    private void setMTareaM(MATareaMVO bean, ResultSet rs) throws SQLException {
   bean.setNUM_MEATAM_PK(rs.getInt("NUM_MEATAM_PK"));
   bean.setTX_DESCATAREA(rs.getString("TX_DESCATAREA"));
   bean.setTX_UMAATAREA(rs.getString("TX_UMAATAREA"));
   bean.setNUM_MES1MEATAM(rs.getDouble("NUM_MES1MEATAM"));
   bean.setNUM_MES2MEATAM(rs.getDouble("NUM_MES2MEATAM"));
   bean.setNUM_MES3MEATAM(rs.getDouble("NUM_MES3MEATAM"));
   bean.setNUM_MES4MEATAM(rs.getDouble("NUM_MES4MEATAM"));
   bean.setNUM_MES5MEATAM(rs.getDouble("NUM_MES5MEATAM"));
   bean.setNUM_MES6MEATAM(rs.getDouble("NUM_MES6MEATAM"));
   bean.setNUM_MES7MEATAM(rs.getDouble("NUM_MES7MEATAM"));
   bean.setNUM_MES8MEATAM(rs.getDouble("NUM_MES8MEATAM"));
   bean.setNUM_MES9MEATAM(rs.getDouble("NUM_MES9MEATAM"));
   bean.setNUM_MES10MEATAM(rs.getDouble("NUM_MES10MEATAM"));
   bean.setNUM_MES11MEATAM(rs.getDouble("NUM_MES11MEATAM"));
   bean.setNUM_MES12MEATAM(rs.getDouble("NUM_MES12MEATAM"));
   bean.setTX_TIPOATAREAM1(rs.getInt("TX_TIPOATAREAM1"));

    }

      private void setMActividadCombo(MATareaVO bean, ResultSet rs) throws SQLException {
//        bean.setNUM_CODACTPER_PK(rs.getInt("NUM_CODACTPER_PK"));
//        bean.setNUM_CODMETAAPER_PK(rs.getInt("NUM_CODMETAAPER_PK"));
//        bean.setTX_UMACTPER(rs.getString("TX_UMACTPER"));
//        bean.setNUM_CANTACTPER(rs.getInt("NUM_CANTACTPER"));
//        bean.setTX_TIPOACTPER(rs.getString("TX_TIPOACTPER"));
  }



    public String spMan(int tipo, MATareaVO bean) {
        String msj = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            CallableStatement cs = conn.prepareCall("{call Man_MATarea(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
            cs.setInt(1, tipo);
            cs.setDouble(2, bean.getNUM_MEATA_PK());
            cs.setDouble(3, bean.getNUM_CODATAREA_PK());
            cs.setDouble(4, bean.getNUM_MES1MEATA());
            cs.setDouble(5, bean.getNUM_MES2MEATA());
            cs.setDouble(6, bean.getNUM_MES3MEATA());
            cs.setDouble(7, bean.getNUM_MES4MEATA());
            cs.setDouble(8, bean.getNUM_MES5MEATA());
            cs.setDouble(9, bean.getNUM_MES6MEATA());
            cs.setDouble(10, bean.getNUM_MES7MEATA());
            cs.setDouble(11, bean.getNUM_MES8MEATA());
            cs.setDouble(12, bean.getNUM_MES9MEATA());
            cs.setDouble(13, bean.getNUM_MES10MEATA());
            cs.setDouble(14, bean.getNUM_MES11MEATA());
            cs.setDouble(15, bean.getNUM_MES12MEATA());
            cs.setDouble(16, bean.getTX_TIPOATAREA1());
            
            cs.execute();



        } catch (Exception e) {
         JOptionPane.showMessageDialog(null, e.toString());
            return msj;
        }
        return msj;
    }

    public String spManM(int tipo, MATareaMVO bean) {
        String msj = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            CallableStatement cs = conn.prepareCall("{call Man_MATareaM(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
            cs.setInt(1, tipo);
            cs.setDouble(2, bean.getNUM_MEATAM_PK());
            cs.setDouble(3, bean.getNUM_CODATAREAM_PK());
            cs.setDouble(4, bean.getNUM_MES1MEATAM());
            cs.setDouble(5, bean.getNUM_MES2MEATAM());
            cs.setDouble(6, bean.getNUM_MES3MEATAM());
            cs.setDouble(7, bean.getNUM_MES4MEATAM());
            cs.setDouble(8, bean.getNUM_MES5MEATAM());
            cs.setDouble(9, bean.getNUM_MES6MEATAM());
            cs.setDouble(10, bean.getNUM_MES7MEATAM());
            cs.setDouble(11, bean.getNUM_MES8MEATAM());
            cs.setDouble(12, bean.getNUM_MES9MEATAM());
            cs.setDouble(13, bean.getNUM_MES10MEATAM());
            cs.setDouble(14, bean.getNUM_MES11MEATAM());
            cs.setDouble(15, bean.getNUM_MES12MEATAM());
            cs.setDouble(16, bean.getTX_TIPOATAREAM1());

            cs.execute();



        } catch (Exception e) {
         JOptionPane.showMessageDialog(null, e.toString());
            return msj;
        }
        return msj;
    }




    

    public ArrayList<MATareaVO> selectLista() throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<MATareaVO> lista = new ArrayList<MATareaVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "select NUM_CODMETAAPER_PK,TX_UMACTPER from ME_METAAPER";
            rs = stmt.executeQuery(sql);
            MATareaVO bean = null;
            while (rs.next()) {
                bean = new MATareaVO();
                setMTarea(bean, rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }

    public List getlist() throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<MATareaVO> lista = new ArrayList<MATareaVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "select NUM_CODMETAAPER_PK,TX_UMACTPER from ME_METAAPER";
            rs = stmt.executeQuery(sql);
            MATareaVO bean = null;
            while (rs.next()) {
                bean = new MATareaVO();
                setMTarea(bean, rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }

    public MATareaVO selectById(double id) throws SQLException {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
      
        MATareaVO bean = null;
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            String sql = "select mt.NUM_MEATA_PK,mt.TX_TIPOATAREA1,ta.TX_DESCATAREA,ta.TX_UMAATAREA,mt.NUM_MES1MEATA,mt.NUM_MES2MEATA,mt.NUM_MES3MEATA, " +
                    "mt.NUM_MES4MEATA,mt.NUM_MES5MEATA,mt.NUM_MES5MEATA,mt.NUM_MES6MEATA,mt.NUM_MES7MEATA,mt.NUM_MES8MEATA,mt.NUM_MES9MEATA,mt.NUM_MES10MEATA " +
                    ",mt.NUM_MES11MEATA,mt.NUM_MES12MEATA " +
                    "from ME_METAATA mt inner join ME_ATAREA ta " +
                    "on mt.NUM_CODATAREA_PK=ta.NUM_CODATAREA_PK" +
                    "where mt.NUM_CODATAREA_PK=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setDouble(1, id);
            rs = pstmt.executeQuery();
            bean = new MATareaVO();
            if (rs.next()) {
                setMTarea(bean, rs);
            }
            rs.close();
            pstmt.close();
        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (pstmt != null) {
                pstmt.close();
                pstmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }
        }
        return bean;
    }


    public int getMuestramatarea(int id) throws SQLException {
        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<MATareaVO> lista = new ArrayList<MATareaVO>(0);
        int numero=0;
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            sql = "select NUM_MEATA_PK from ME_METAATA WHERE NUM_CODATAREA_PK=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            MATareaVO bean = null;
            while (rs.next()) {
                bean = new MATareaVO();
                setMuestraM(bean, rs);
                lista.add(bean);
                numero=1;
            }
            rs.close();
            pstmt.close();
        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return numero;
    }


    public int getMuestramatareaMod(int id) throws SQLException {
        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<MATareaVO> lista = new ArrayList<MATareaVO>(0);
        int numero=0;
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            sql = "select NUM_MEATAM_PK from ME_METAATAM WHERE NUM_CODATAREA_PK=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            MATareaVO bean = null;
            while (rs.next()) {
                bean = new MATareaVO();
                setMuestraMod(bean, rs);
                lista.add(bean);
                numero=1;
            }
            rs.close();
            pstmt.close();
        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return numero;
    }



    public int maximo() throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        String sql = "";
        int max=0;
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "select max(NUM_MEATA_PK) AS NUM_MEATA_PK FROM ME_METAATA";
            rs = stmt.executeQuery(sql);
            MATareaVO bean = null;
            while (rs.next()) {
                bean = new MATareaVO();
                setMetaActividadmax(bean, rs);
                max=bean.getNUM_MEATA_PK();
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return max;
    }

   




    


     public List getListById(double id) throws SQLException {
        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<MATareaVO> lista = new ArrayList<MATareaVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
             sql = "select mt.NUM_MEATA_PK,mt.TX_TIPOATAREA1,ta.TX_DESCATAREA,ta.TX_UMAATAREA,mt.NUM_MES1MEATA,mt.NUM_MES2MEATA,mt.NUM_MES3MEATA, " +
                    "mt.NUM_MES4MEATA,mt.NUM_MES5MEATA,mt.NUM_MES5MEATA,mt.NUM_MES6MEATA,mt.NUM_MES7MEATA,mt.NUM_MES8MEATA,mt.NUM_MES9MEATA,mt.NUM_MES10MEATA " +
                    ",mt.NUM_MES11MEATA,mt.NUM_MES12MEATA " +
                    "from ME_METAATA mt inner join ME_ATAREA ta " +
                    "on mt.NUM_CODATAREA_PK=ta.NUM_CODATAREA_PK " +
                    "where mt.NUM_CODATAREA_PK=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setDouble(1, id);
            rs = pstmt.executeQuery();
            MATareaVO bean = null;
            while (rs.next()) {
                bean = new MATareaVO();
                setMTarea(bean, rs);
                lista.add(bean);
            }
            rs.close();
            pstmt.close();
        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }


     public List getListByIdM(double id) throws SQLException {
        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<MATareaMVO> lista = new ArrayList<MATareaMVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
             sql = "select mt.NUM_MEATAM_PK,mt.TX_TIPOATAREAM1,ta.TX_DESCATAREAM,ta.TX_UMAATAREAM,mt.NUM_MES1MEATAM,mt.NUM_MES2MEATAM,mt.NUM_MES3MEATAM, " +
                    "mt.NUM_MES4MEATAM,mt.NUM_MES5MEATAM,mt.NUM_MES5MEATAM,mt.NUM_MES6MEATAM,mt.NUM_MES7MEATAM,mt.NUM_MES8MEATAM,mt.NUM_MES9MEATAM,mt.NUM_MES10MEATAM " +
                    ",mt.NUM_MES11MEATAM,mt.NUM_MES12MEATAM " +
                    "from ME_METAATAM mt inner join ME_ATAREAM ta " +
                    "on mt.NUM_CODATAREAM_PK=ta.NUM_CODATAREAM_PK " +
                    "where mt.NUM_CODATAREAM_PK=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setDouble(1, id);
            rs = pstmt.executeQuery();
            MATareaMVO bean = null;
            while (rs.next()) {
                bean = new MATareaMVO();
                setMTareaM(bean, rs);
                lista.add(bean);
            }
            rs.close();
            pstmt.close();
        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }



     public List getListByIdMod(double id) throws SQLException {
        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<MATareaMVO> lista = new ArrayList<MATareaMVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
             sql = "select mt.NUM_MEATAM_PK,mt.TX_TIPOATAREAM1,ta.TX_DESCATAREA,ta.TX_UMAATAREA,mt.NUM_MES1MEATAM,mt.NUM_MES2MEATAM,mt.NUM_MES3MEATAM, mt.NUM_MES4MEATAM,mt.NUM_MES5MEATAM,mt.NUM_MES5MEATAM,mt.NUM_MES6MEATAM,mt.NUM_MES7MEATAM,mt.NUM_MES8MEATAM,mt.NUM_MES9MEATAM,mt.NUM_MES10MEATAM ,mt.NUM_MES11MEATAM,mt.NUM_MES12MEATAM from ME_METAATAM mt inner join ME_ATAREA ta on mt.NUM_CODATAREA_PK=ta.NUM_CODATAREA_PK where mt.NUM_CODATAREA_PK=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setDouble(1, id);
            rs = pstmt.executeQuery();
            MATareaMVO bean = null;
            while (rs.next()) {
                bean = new MATareaMVO();
                setMTareaM(bean, rs);
                lista.add(bean);
            }
            rs.close();
            pstmt.close();
        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }



     public List getCombo(int id) throws SQLException {
        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<MATareaVO> lista = new ArrayList<MATareaVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);

            sql = "select TX_UMACTPER,NUM_CANTACTPER,TX_TIPOACTPER,NUM_CODMETAAPER_PK,NUM_CODACTPER_PK from ME_METAAPER where NUM_CODACTPER_PK=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            MATareaVO bean = null;
            while (rs.next()) {
                bean = new MATareaVO();
                setMActividadCombo(bean, rs);
                lista.add(bean);
            }
            rs.close();
            pstmt.close();
        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }



}
